/*     */ package btsm.bean;
/*     */ 
/*     */ import java.io.FileInputStream;
/*     */ import java.io.FileOutputStream;
/*     */ import java.io.PrintStream;
/*     */ import java.sql.ResultSet;
/*     */ import java.text.ParseException;
/*     */ import java.text.SimpleDateFormat;
/*     */ import java.util.Date;
/*     */ import org.apache.poi.hssf.usermodel.HSSFCell;
/*     */ import org.apache.poi.hssf.usermodel.HSSFDateUtil;
/*     */ import org.apache.poi.hssf.usermodel.HSSFRow;
/*     */ import org.apache.poi.hssf.usermodel.HSSFSheet;
/*     */ import org.apache.poi.hssf.usermodel.HSSFWorkbook;
/*     */ import org.apache.poi.poifs.filesystem.POIFSFileSystem;
/*     */ 
/*     */ public class excel
/*     */ {
/*     */   dbConnector dbcon;
/*     */ 
/*     */   public excel()
/*     */   {
/*  22 */     this.dbcon = new dbConnector();
/*     */   }
/*     */ 
/*     */   public void all_close() {
/*  26 */     this.dbcon.closeAll();
/*     */   }
/*     */ 
/*     */   public static String DateToStr(Date date) {
/*  30 */     SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
/*  31 */     String str = format.format(date);
/*  32 */     return str;
/*     */   }
/*     */ 
/*     */   public static Date StrToDate(String str) {
/*  36 */     SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
/*  37 */     Date date = null;
/*     */     try {
/*  39 */       date = format.parse(str);
/*     */     } catch (ParseException e) {
/*  41 */       e.printStackTrace();
/*     */     }
/*  43 */     return date;
/*     */   }
/*     */ 
/*     */   public Boolean ReadRowCell(String rexcelpath, int rrowno, int rcol, String rviewname) {
/*  47 */     Boolean flag = Boolean.valueOf(true);
/*  48 */     FileInputStream fileIn = null;
/*  49 */     int rowtotal = 0;
/*  50 */     int rownum = 0;
/*  51 */     int cellnum = 0;
/*  52 */     String ssssql = "";
/*     */     try
/*     */     {
/*  55 */       fileIn = new FileInputStream(rexcelpath);
/*     */     } catch (Exception ex) {
/*  57 */       ex.printStackTrace();
/*     */ 
/*  59 */       flag = Boolean.valueOf(false);
/*     */     }
/*     */ 
/*     */     try
/*     */     {
/*  65 */       POIFSFileSystem fs = new POIFSFileSystem(fileIn);
/*  66 */       HSSFWorkbook wb = new HSSFWorkbook(fs);
/*     */ 
/*  68 */       HSSFSheet sheet = wb.getSheetAt(0);
/*     */ 
/*  70 */       rowtotal = sheet.getPhysicalNumberOfRows();
/*  71 */       rownum = rowtotal - rrowno;
/*  72 */       HSSFRow row = sheet.getRow(rrowno);
/*  73 */       cellnum = rcol;
/*     */ 
/*  75 */       String[] readrow = new String[cellnum];
/*     */ 
/*  77 */       int celltype = 0;
/*     */ 
/*  80 */       for (int i = 0; i < rownum; i++) {
/*  81 */         int k = 0;
/*  82 */         int kcellnum = 0;
/*  83 */         row = sheet.getRow(i + rrowno);
/*  84 */         for (int j = 0; j < cellnum; j++) {
/*  85 */           HSSFCell cell = row.getCell(j);
/*  86 */           celltype = cell.getCellType();
/*     */ 
/*  88 */           switch (celltype) {
/*     */           case 0:
/*  90 */             if (HSSFDateUtil.isCellDateFormatted(cell))
/*  91 */               readrow[j] = DateToStr(cell.getDateCellValue());
/*     */             else {
/*  93 */               readrow[j] = String.valueOf(cell.getNumericCellValue());
/*     */             }
/*     */ 
/*  96 */             break;
/*     */           case 1:
/*  98 */             readrow[j] = cell.getStringCellValue();
/*  99 */             break;
/*     */           case 2:
/* 101 */             readrow[j] = String.valueOf(cell.getNumericCellValue());
/* 102 */             break;
/*     */           case 3:
/* 105 */             readrow[j] = "";
/* 106 */             break;
/*     */           default:
/* 108 */             readrow[j] = "";
/*     */           }
/*     */ 
/* 112 */           if (k < cellnum - 1) {
/* 113 */             ssssql = ssssql + '"' + readrow[k] + '"' + ",";
/* 114 */             k++;
/*     */           }
/*     */ 
/*     */         }
/*     */ 
/* 121 */         ssssql = ssssql + '"' + readrow[(cellnum - 1)] + '"';
/*     */ 
/* 124 */         String ssql = "insert into " + rviewname + " values(" + ssssql + ");";
/*     */ 
/* 126 */         for (int jk = 0; jk < cellnum; jk++)
/*     */         {
/* 128 */           if (readrow[jk].equals("")) {
/* 129 */             kcellnum++;
/*     */           }
/*     */         }
/* 132 */         if (kcellnum != cellnum)
/*     */         {
/* 135 */           this.dbcon.execUpdate(ssql);
/*     */         }
/*     */ 
/* 138 */         ssssql = "";
/* 139 */         kcellnum = 0;
/*     */       }
/*     */ 
/* 143 */       if (fileIn != null) {
/* 144 */         fileIn.close();
/*     */       }
/*     */ 
/* 147 */       this.dbcon.closeAll();
/*     */     }
/*     */     catch (Exception ex) {
/* 150 */       ex.printStackTrace();
/*     */     }
/*     */ 
/* 159 */     return flag;
/*     */   }
/*     */ 
/*     */   public Boolean WriteRowCell(String sexcelpath, String wexcelpath, int wrowno, int wcol, String wviewname) {
/* 163 */     boolean flag = true;
/*     */ 
/* 165 */     FileInputStream fileIn = null;
/* 166 */     FileOutputStream fileOut = null;
/*     */     try {
/* 168 */       fileIn = new FileInputStream(sexcelpath);
/*     */     } catch (Exception ex) {
/* 170 */       ex.printStackTrace();
/*     */ 
/* 172 */       flag = false;
/*     */     }
/*     */ 
/*     */     try
/*     */     {
/* 178 */       POIFSFileSystem fs = new POIFSFileSystem(fileIn);
/*     */ 
/* 180 */       HSSFWorkbook wb = new HSSFWorkbook(fs);
/* 181 */       HSSFSheet sheet = wb.getSheetAt(0);
/*     */ 
/* 184 */       int i = 0;
/* 185 */       String sql = "select * from " + wviewname;
/* 186 */       ResultSet rs = this.dbcon.execQuery(sql);
/*     */ 
/* 188 */       int coltotal = wcol;
/*     */ 
/* 190 */       int colno = 0;
/*     */ 
/* 192 */       int colnum = coltotal - colno;
/* 193 */       String[] writerow = new String[colnum];
/*     */ 
/* 197 */       while (rs.next()) {
/* 198 */         HSSFRow row = sheet.getRow(i + wrowno);
/* 199 */         if (row == null) {
/* 200 */           row = sheet.createRow(i + wrowno);
/*     */         }
/*     */ 
/* 203 */         for (int j = colno; j < colnum; j++)
/*     */         {
/* 205 */           writerow[j] = rs.getString(j + 1);
/*     */ 
/* 207 */           HSSFCell cell = row.getCell((short)j);
/* 208 */           if (cell == null) {
/* 209 */             cell = row.createCell((short)j);
/*     */           }
/* 211 */           cell.setCellType(1);
/* 212 */           cell.setCellValue(writerow[j]);
/*     */         }
/* 214 */         i++;
/*     */       }
/*     */ 
/* 218 */       fileOut = new FileOutputStream(wexcelpath);
/*     */ 
/* 222 */       wb.write(fileOut);
/*     */ 
/* 224 */       if (fileOut != null) {
/* 225 */         fileOut.close();
/*     */       }
/* 227 */       if (fileIn != null) {
/* 228 */         fileIn.close();
/*     */       }
/* 230 */       this.dbcon.closeAll();
/*     */     }
/*     */     catch (Exception ex) {
/* 233 */       ex.printStackTrace();
/* 234 */       System.out.println("发生异常.");
/*     */     }
/* 236 */     return Boolean.valueOf(flag);
/*     */   }
/*     */ }

/* Location:           E:\java\apache-tomcat-6.0.36\webapps\btsm\WEB-INF\classes\
 * Qualified Name:     btsm.bean.excel
 * JD-Core Version:    0.6.2
 */